--delete from  cat_job
-- select * from cat_job
INSERT INTO cat_job
SELECT
case len(convert(varchar(10),id))
    when 5 then '00000000-0000-0000-0000-0000000' + convert(varchar(10),id) 
    when 6 then '00000000-0000-0000-0000-000000' + convert(varchar(10),id) end AS Id ,
JobName AS [JobTitle],NULL AS [ReferenceNumber],PositionChar AS [JobType],Null AS [Department],NULL AS [Welfare],MAJOR AS [Major1],MAJOR1 AS [Major2],LowEducation AS [EducationLevel],IncludeHigherEducation AS [IsDegreeAbove],LowExperience AS [YearsOfExperience],NULL AS [CareerLevel],AgeMin AS [MinAge],AgeMax AS [MaxAge],JobDetail + char(13) + char(13) +  JobCommandDetail AS [JobDescription],NULL AS [Keywords],NULL AS [Source],CreatedDate AS [DateCreated],UpdatedDate AS [LastUpdated],case len(convert(varchar(10),companyid))
    when 5 then '00000000-0000-0000-0000-0000000' + convert(varchar(10),companyid) 
    when 4 then '00000000-0000-0000-0000-00000000' + convert(varchar(10),companyid) 
    when 3 then '00000000-0000-0000-0000-000000000' + convert(varchar(10),companyid) 
    when 2 then '00000000-0000-0000-0000-0000000000' + convert(varchar(10),companyid) 
    when 1 then '00000000-0000-0000-0000-00000000000'+ convert(varchar(10),companyid) 
    end AS [ModifiedBy],0 AS [Deleted],case len(convert(varchar(10),companyid))
    when 5 then '00000000-0000-0000-0000-0000000' + convert(varchar(10),companyid) 
    when 4 then '00000000-0000-0000-0000-00000000' + convert(varchar(10),companyid) 
    when 3 then '00000000-0000-0000-0000-000000000' + convert(varchar(10),companyid) 
    when 2 then '00000000-0000-0000-0000-0000000000' + convert(varchar(10),companyid) 
    when 1 then '00000000-0000-0000-0000-00000000000'+ convert(varchar(10),companyid) 
    end AS [CompanyId]FROM srv_lnk.nchr2008.dbo.Companyjob
where enddate >='2012-10-29' AND jobtype <> 135002
AND companyid in(select id from companyinfo)